package com.example.mapp.buildSql;

import com.example.entity.paperlessMaintance.PaperlessMaintanceApply;
import com.example.ext.entity.ExtElevator;
import com.example.util.StringUtil;
import org.apache.commons.lang3.StringUtils;

import java.util.Map;

public class PaperlessMaintMappSql {

    public String queryPaperlessMaint(PaperlessMaintanceApply paperlessMaintanceApply){
        StringBuilder builder = new StringBuilder("select pma.*,p.party_name as 'jgPartyName' from paperless_maintance_apply pma LEFT JOIN party p ON pma.jg_party_id = p.id where 1=1 ");
        if(StringUtils.isNotEmpty(paperlessMaintanceApply.getJgPartyId())){
            builder.append(" and jg_party_id = #{jgPartyId}");
            builder.append(" and apply_status not in('0')");
        }
        if(StringUtils.isNotEmpty(paperlessMaintanceApply.getParentParentPartyId())){
            builder.append(" and parent_parent_party_id = #{parentParentPartyId}");
            builder.append(" and apply_status not in('0','1')");
        }
        if(StringUtils.isNotEmpty(paperlessMaintanceApply.getUserPartyId())){
            builder.append(" and user_party_id = #{userPartyId}");
        }
        if(StringUtils.isNotEmpty(paperlessMaintanceApply.getUserPartyName())){
            String tempPartyName = "'%"+ paperlessMaintanceApply.getUserPartyName()+"%'";
            builder.append(" and user_party_name like ").append(tempPartyName);
        }
        if(StringUtils.isNotEmpty(paperlessMaintanceApply.getMaintenancePartyId())){
            builder.append(" and maintenance_party_id = #{maintenancePartyId} ");
        }
        if(StringUtils.isNotEmpty(paperlessMaintanceApply.getMaintenancePartyName())){
            String tempPartyName = "'%"+paperlessMaintanceApply.getMaintenancePartyName()+"%'";
            builder.append(" and maintenance_party_name like ").append(tempPartyName);
        }
        if(StringUtils.isNotEmpty(paperlessMaintanceApply.getApplyStatus())){
            builder.append(" and apply_status = #{applyStatus}");
        }
        if(StringUtils.isNotEmpty(paperlessMaintanceApply.getStartDate())){
            builder.append(" and apply_date > #{startDate}");
        }
        if(StringUtils.isNotEmpty(paperlessMaintanceApply.getEndDate())){
            builder.append(" and apply_date <= #{endDate}");
        }
        return builder.toString();
    }

    public String updatePaperlessMaint(PaperlessMaintanceApply paperlessMaintanceApply){
        StringBuilder builder = new StringBuilder("update paperless_maintance_apply set ");
        StringBuilder setValue = new StringBuilder();
        if(StringUtils.isNotEmpty(paperlessMaintanceApply.getApplyStatus())){
            setValue.append(" apply_status = #{applyStatus},");
        }
        if(StringUtils.isNotEmpty(paperlessMaintanceApply.getLetterOfCommitmentDoc())){
            setValue.append(" letter_of_commitment_doc = #{letterOfCommitmentDoc},");
        }
        if(StringUtils.isNotEmpty(paperlessMaintanceApply.getRemarks())){
            setValue.append(" remarks = #{remarks},");
        }
        if(StringUtils.isNotEmpty(paperlessMaintanceApply.getApplyDate())){
            setValue.append(" apply_date = #{applyDate},");
        }
        if(StringUtils.isNotEmpty(paperlessMaintanceApply.getSuspendReason())){
            setValue.append(" suspend_reason = #{suspendReason},");
        }
        if(StringUtils.isNotEmpty(paperlessMaintanceApply.getSuspendRemarks())){
            setValue.append(" suspend_remarks = #{suspendRemarks},");
        }
        if(StringUtils.isNotEmpty(paperlessMaintanceApply.getOtherNotes())){
            builder.append(" other_notes = #{otherNotes},");
        }
        if(StringUtils.isNotEmpty(paperlessMaintanceApply.getCreateCompanyName())){
            builder.append(" create_company_name = #{createCompanyName},");
        }
        if(StringUtils.isNotEmpty(paperlessMaintanceApply.getJgPartyId())){
            builder.append(" jg_party_id = #{jgPartyId},");
        }
        if(StringUtils.isNotEmpty(paperlessMaintanceApply.getJgPartyName())){
            builder.append(" jg_party_name = #{jgPartyName},");
        }
        if(StringUtils.isNotEmpty(paperlessMaintanceApply.getUserPartyId())){
            builder.append(" user_party_id = #{userPartyId},");
        }
        if(StringUtils.isNotEmpty(paperlessMaintanceApply.getUserPartyName())){
            builder.append(" user_party_name = #{userPartyName},");
        }
        if(StringUtils.isNotEmpty(paperlessMaintanceApply.getElevatorIds())){
            builder.append(" elevator_ids = #{elevatorIds},");
        }
        builder.append(setValue.substring(0,setValue.lastIndexOf(","))).append(" where 1=1 ");
        if(paperlessMaintanceApply.getId() != null){
            builder.append(" and id = #{id}");
        }

        return builder.toString();
    }

    /**
     * 无纸化维保获取可选的申请的对象（区级监管单位）
     * @param extElevator  f.parent_id as parentId,\n" +
     * @return
     */
    public String getOptionalJgParty (ExtElevator extElevator){
        StringBuilder builder = new StringBuilder();
        builder.append("select sum(case when isMaint = '是' then 1 else 0 end) as isMaintCount,\n" +
                "       sum(case when isMaint = '否' then 1 else 0 end) as isNotMaintCount, id,partyName from (SELECT C.id ,c.party_name as partyName,C.party_level,A.maintenance_party_id,\n" +
                "    A.used_party_id,A.create_company_name, case when maintCount > 2 then '是' else '否' end as isMaint FROM elevator.elevator A LEFT JOIN elevator.sub_party_info B ON A.branch_office = B.id\n" +
                "    LEFT JOIN elevator.party C ON B.parent_id = C.id\n" +
                "    left join (select COUNT(1) as maintCount,elevator_num from elevator.traction_half_mon  where elevator_num is not null group by elevator_num) D\n" +
                "                    on A.elevator_num = D.elevator_num) S where party_level = '2'" );

        /*if(extElevator.getUsedPartyId() != null){
            builder.append(" and used_party_id = #{usedPartyId}");
        }
        if(extElevator.getMaintenancePartyId() != null){
            builder.append(" and maintenance_party_id = #{maintenancePartyId}");
        }*/

        //制造单位，可能多个值
        /*String createCompanyName = extElevator.getCreateCompanyName();
        if(StringUtils.isNotEmpty(createCompanyName)){
            String [] names = createCompanyName.split(",");
            StringBuilder sb = new StringBuilder(" and create_company_name in (");
            for(int i=0, len = names.length; i < len; i++){
                if(i == len - 1){
                    sb.append("'").append(names[i]).append("')");
                }else{
                    sb.append("'").append(names[i]).append("',");
                }
            }
            builder.append(sb);
        }*/
        builder.append("group by id");
        return builder.toString();
    }

    public String paperlessGetAreaElevatorList (Map map){
        StringBuilder builder = new StringBuilder();

        builder.append(" SELECT er.id as 'id',");
        builder.append(" er.elevator_num as 'elevatorNum', ");
        builder.append(" p1.party_name as 'usedPartyName', ");
        builder.append(" et.type_name as 'elevatorTypeName', ");
        builder.append(" er.build_addr as 'buildAddr', ");
        builder.append(" '1' as 'isFlag', ");
        builder.append(" '是' as 'isMaint', ");
        builder.append(" su.user_name as 'safeUserName', ");
        builder.append(" su2.user_name as 'maintenanceUserFirstName', ");
        builder.append(" er.is_paperless_maintenance as 'isPaperlessMaintenance', ");
        builder.append(" su2.firstgt as 'firstgt' ");
        builder.append(" FROM elevator er ");
        builder.append(" LEFT JOIN elevator_type et ON et.id = er.elevator_type_id ");
        builder.append(" LEFT JOIN party p1 ON p1.id = er.used_party_id ");
        builder.append(" LEFT JOIN sys_user su ON su.id = er.safe_user_id ");
        builder.append(" LEFT JOIN sys_user su2 ON su2.id = er.maintenance_user_first ");
        builder.append(" WHERE er.branch_office IN (SELECT id ");
        builder.append(" FROM party ");
        builder.append(" WHERE parent_id = #{jgPartyId}) ");
        builder.append(" AND er.maintenance_party_id = #{maintenancePartyId} ");

        if(map.get("elevatorNum") != null){
            builder.append(" and er.elevator_num = #{elevatorNum}");
        }
        if(map.get("elevatorTypeId") != null){
            builder.append(" and et.id = #{elevatorTypeId}");
        }

        if(map.get("elevators") !=null ){
            String elevators = map.get("elevators").toString();
            if (StringUtil.isNotBlank(elevators)) {
                String[] ids = elevators.split(",");
                StringBuilder sb = new StringBuilder(" and er.id in (");
                for (int i = 0, len = ids.length; i < len; i++) {
                    if (i == len - 1) {
                        sb.append("'").append(ids[i]).append("')");
                    } else {
                        sb.append("'").append(ids[i]).append("',");
                    }
                }
                builder.append(sb);
            }
        }
        return builder.toString();
    }



}
